﻿using FluentData;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace Common.Utility
{
    public class DBContext : BaseSqlConnection
    {
        #region 增加方法
        public virtual int Add<T>(T t)
        {
            if (t == null)
                throw new ArgumentNullException("ExecuteReturnLastId", "实体数据填充调用方法不能为空!");

            var obj = Context.Insert(typeof(T).Name);

            foreach (var item in t.GetType().GetProperties())
            {
                obj.Column(item.Name, item.GetValue(t, null));
            }
            return obj.Execute();
        }


        public virtual T AddReturnModel<T>(T t)
        {
            if (t == null)
                throw new ArgumentNullException("ExecuteReturnLastId", "实体数据填充调用方法不能为空!");

            var obj = Context.Insert(typeof(T).Name);

            foreach (var item in t.GetType().GetProperties())
            {
                obj.Column(item.Name, item.GetValue(t, null));
            }
            return obj.ExecuteReturnLastId<T>();
        }
        #endregion


        /// <summary>
        ///返回受影响的行数
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public virtual int Execute(string sqlstr)
        {
            return Context.Sql(sqlstr).Execute();
        }


        #region 修改方法

        public virtual int Update<T>(T t)
        {
            if (t == null)
                throw new ArgumentNullException("Update", "修改的model值不能为空!");

            var obj = Context.Update(typeof(T).Name);

            foreach (var item in t.GetType().GetProperties())
            {
                var tvalue = item.GetValue(t, null);
                if (tvalue != null)
                {
                    var keyAttributes = item.GetCustomAttributes(typeof(QueryColumnNameAttribute), true);
                    if (keyAttributes.Length > 0)
                    {
                        var cnAttribute = keyAttributes[0] as QueryColumnNameAttribute;
                        if (cnAttribute != null)
                        {
                            obj.Where(item.Name, tvalue);
                        }
                    }
                    else
                    {
                        obj.Column(item.Name, tvalue);
                    }
                }
            }
            return obj.Execute();
        }

        #endregion

        #region 是否存在
        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="dicp"></param>
        /// <returns></returns>
        public virtual bool Exists(string sqlStr, Dictionary<string, object> dicp = null)
        {
            bool existsFlag = true;
            var obj = Context.Sql(sqlStr);
            foreach (var item in dicp)
            {
                obj.Parameter(item.Key, item.Value);
            }
            var query = obj.QuerySingle<DataTable>();

            if (query.Rows.Count < 1)
            {
                existsFlag = false;
            }
            return existsFlag;
        }

        public virtual bool Exists<T>(T t)
        {
            bool existsFlag = true;
            DataTable dt = null;
            foreach (var pi in (typeof(T)).GetProperties())
            {
                var keyAttributes = pi.GetCustomAttributes(typeof(QueryColumnNameAttribute), true);
                if (keyAttributes.Length > 0)
                {
                    var cnAttribute = keyAttributes[0] as QueryColumnNameAttribute;
                    if (cnAttribute != null)
                    {
                        var sqlstr = string.Format("select {1} from {0} where {1}= '{2}'", typeof(T).Name, cnAttribute.CName, pi.GetValue(t, null));
                        Context.Sql(sqlstr).QuerySingle<DataTable>();
                    }
                }
            }

            if (dt.Rows.Count < 1)
            {
                existsFlag = false;
            }
            return existsFlag;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="t"></param>
        /// <param name="modelName"></param>
        public virtual void Exists<T>(T t, string modelName)
        {

        }
        #endregion

        #region 查询数据


        /// <summary>
        /// 根据sql查询数据返回datatable
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public virtual DataTable QueryTable(string sqlstr)
        {
            return Context.Sql(sqlstr).QuerySingle<DataTable>();
        }

        /// <summary>
        /// 根据主键查询出当前Model
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public virtual T Find<T>(T t)
        {
            foreach (var pi in (typeof(T)).GetProperties())
            {
                var keyAttributes = pi.GetCustomAttributes(typeof(QueryColumnNameAttribute), true);
                if (keyAttributes.Length > 0)
                {

                    var cnAttribute = keyAttributes[0] as QueryColumnNameAttribute;
                    if (cnAttribute != null)
                    {
                        var sqlstr = string.Format("select * from {0} where {1}= '{2}'", typeof(T).Name, cnAttribute.CName, pi.GetValue(t, null));
                        t = Context.Sql(sqlstr).QuerySingle<T>();
                    }
                }
            }
            return t;
        }


        public virtual T Find<T>(string t) where T : new()
        {
            T model = new T();
            foreach (var pi in (typeof(T)).GetProperties())
            {
                var keyAttributes = pi.GetCustomAttributes(typeof(QueryColumnNameAttribute), true);
                if (keyAttributes.Length > 0)
                {
                    var cnAttribute = keyAttributes[0] as QueryColumnNameAttribute;
                    if (cnAttribute != null)
                    {
                        var sqlstr = string.Format("select * from {0} where {1}= '{2}'", typeof(T).Name, cnAttribute.CName, t);
                        model = Context.Sql(sqlstr).QuerySingle<T>();
                    }
                }
            }
            return model;
        }

        public virtual TEntity SqlFindFirst<TEntity>(string sql, object[] paras = null)
        {
            var obj = Context.Sql(sql, paras);
            return obj.QueryMany<TEntity>().FirstOrDefault();
        }


        public virtual List<T> FindByColumns<T>(ParamWhere[] paras = null)
        {
            var sbwhere = new StringBuilder();
            var objpars = new ArrayList();
            var sqlstr = "select * from " + typeof(T).Name;
            var listModel = new List<T>();
            foreach (var item in paras)
            {
                objpars.Add(item.Pars.ValueParam);
                sbwhere.AppendFormat(item.GetWhereStr());
            }

            if (objpars.Count > 0)
            {
                sqlstr += " where " + sbwhere.ToString();
                var obj = Context.Sql(sqlstr).Parameters(objpars.ToArray());
                listModel = obj.QueryMany<T>();
            }
            else
            {
                var obj = Context.Sql(sqlstr).Parameters(objpars.ToArray());
                listModel = obj.QueryMany<T>();
            }

            return listModel;
        }


        public virtual void GetListByColumn()
        {

        }




        public virtual PagerQuery<PageInfo, T> PagerQuerySingle<T>(PageInfo pageInfo, Param[] paras = null)
        {
            string[] arrstr = GetPageInfoSql(pageInfo);
            if (arrstr.Length != 2)
                throw new SystemException("没有获取正确的分页查询语句!");

            var objlist = Context.Sql(arrstr[0]);
            var objcount = Context.Sql(arrstr[1]);
            if (paras != null)
            {
                foreach (var item in paras)
                {
                    objlist.Parameter(item.KeyParam, item.ValueParam);
                    objcount.Parameter(item.KeyParam, item.ValueParam);
                }
            }
            pageInfo.TotalCount = objcount.QuerySingle<int>();
            var listT = objlist.QuerySingle<T>();
            PagerQuery<PageInfo, T> pagerQuery = new PagerQuery<PageInfo, T>(pageInfo, listT);
            return pagerQuery;
        }


        public string[] GetPageInfoSql(PageInfo pageInfo)
        {
            var pageSql = string.Format("SELECT {0} {1} FROM (select ROW_NUMBER() OVER (ORDER BY {2}) as rowNum,{3} from {4} {5}) as MyTable where rowNum between {6} and {7}",
                pageInfo.Distinct ? " distinct " : "", pageInfo.ColumnNames, pageInfo.OrderPlace, pageInfo.ColumnNames, pageInfo.TableName, string.IsNullOrEmpty(pageInfo.StrWhere) ? "" : (" where  " + pageInfo.StrWhere + " "),
                            pageInfo.PageSize * (pageInfo.CurPageIndex - 1) + 1, pageInfo.PageSize * (pageInfo.CurPageIndex));

            var pageCountstr = string.Format("select count(*) num from (select {0} {1} from {2} {3}) as t", pageInfo.Distinct ? " distinct " : "", pageInfo.ColumnNames, pageInfo.TableName, string.IsNullOrEmpty(pageInfo.StrWhere) ? "" : (" where  " + pageInfo.StrWhere + " "));

            return (new string[] { pageSql, pageCountstr });
        }


        private void MapComplexProduct<T>(IList<T> products, dynamic reader) where T : new()
        {
            var a = new T();
        }


        #endregion

        #region 存储过程
        public virtual IStoredProcedureBuilder StoredProcedure(string proName, Param[] parmas = null)
        {
            var obj = Context.StoredProcedure(proName);
            foreach (var item in parmas)
            {
                if (item.InOutType.ToString().Contains("Output"))
                    obj.ParameterOut(item.KeyParam, item.GetParamType(), item.Size);
                else
                    obj.Parameter(item.KeyParam, item.ValueParam, item.GetParamType(), item.Size);
            }
            obj.Execute();
            return obj;
        }
        #endregion

    }
}
